﻿
CREATE PROCEDURE [dbo].[proc_Employee_CompanyPostMove_List]
		@eid int
AS
BEGIN select * from (
	select
		Id, 
		Eid,
		(select EName from Employee E where EId=Id) as EName,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=ACompanyId))) as  AManagementarea,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=BCompanyId))) as  BManagementarea,
		(case ACompanyName when '' then (select CompanyName from Company where id=ACompanyId) else ACompanyName end) as ACompanyName,
		(case BCompanyName when '' then (select CompanyName from Company where id=BCompanyId) else BCompanyName end) as BCompanyName,
		(case ADepartmentName when '' then (select DName from Department where id= (SELECT e.DepartmentId from employee E where Employee_PostMove.EId=E.Id)) else ADepartmentName end) as ADName,
		(case BDepartmentName when '' then (select DName from Department where id= BDepartmentId) else BDepartmentName end) as BDName,
		(case APostName when '' then (select PostName from Post where id= APostId) else APostName end) as APostName,
		
		(case AGradeName when '' then (select Title from Grade G where id= AGradeId) else AGradeName end) as AGradeName,
		--(select Title from Grade G where id= BGradeId) as BGradeName,

		--(select PostName from Post where id= BPostId) as BPostName,
		--(select title from post_category where id=(select postcategory from Post where id= APostId)) as APostCategory,
		--(select title from post_category where id=(select postcategory from Post where id= BPostId)) as BPostCategory,
		--(select Title from Employee_State S where id=AState) as AState,
		AZhiWu,
		BZhiWu,
		--Reason,
		--(select ename from employee where username=Proposer) as Proposer,
		ApplionTime,
		--FirstAudit,
		--FirstMemo,
		--(select ename from employee where username=FirstAuditPerson) as FirstAuditPerson,
		--FirstAuditTime,
		--SecondAudit,
		--SecondMemo,
		--(select ename from employee where username=SecondAuditPerson) as SecondAuditPerson,
		SecondAuditTime,
		ISNULL(Execdate,'1900-01-01') AS Execdate,
		--ISNULL(BXZJB,'') AS BXZJB,
		ISNULL(AXZJB,'') AS AXZJB,'职级异动' as memo
	from
		Employee_PostMove
	where 
		eid=@eid
		and secondAudit=1		
		
		union
		
		select

		Id,		
		EId,
		(select EName from Employee E where EId=E.Id) as EName,
		--(select Sex from Employee E where EId=E.Id) as Sex,
		--(select IDCard from Employee E where EId=E.Id) as IDCard,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=ACompanyId))) as  AManagementarea,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=BCompanyId))) as  BManagementarea,
		(case ACompanyName when '' then (select CompanyName from Company where id=(select Acompanyid from employee E where Id=@eid)) else ACompanyName end) as ACompanyName,
		(case BCompanyName when '' then (select CompanyName from Company where id=(select Bcompanyid from employee E where Id=@eid)) else BCompanyName end) as BCompanyName,
		(case ADepartmentName when '' then (select DName from Department where id=(select Adepartmentid from employee E where Id=@eid)) else ADepartmentName end) as ADepartmentName,
		(case BDepartmentName when '' then (select DName from Department where id=(select Bdepartmentid from employee E where Id=@eid)) else BDepartmentName end) as BDepartmentName,
		--(select EName from Employee E where Employee_CompanyMove.AuditPerson=E.UserName) as AuditPersonName,
		(case PostName when '' then (select PostName from Post P where id=(select PostId from employee E where Employee_CompanyMove.EId=E.Id)) else PostName end) as PostName,
		(case GradeName when '' then (select Title from Grade G where id=(select GradeId from employee E where Employee_CompanyMove.EId=E.Id)) else GradeName end) as GradeName,
		AZhiWu,
		BZhiWu,
		--Reason,
		--ISNULL((select ename from employee WHERE UserName= Proposer),Proposer) AS Proposer,
		ApplionTime,
		--Audit,
		--Memo,
		--ISNULL((select ename from employee WHERE UserName= AuditPerson),AuditPerson) AS AuditPerson,
		AuditTime,
		--ISNULL(BPid,0) AS BPid,
		--(Select p.Title From Province p  where p.Id=BPid) as BProvince,
		--ISNULL(BCid,0) AS BCid,
		--ISNULL((Select c.Title From city c  where c.Id=BCid),'') as BCity,
		--ISNULL(APid,0) AS APid,
		--ISNULL((Select p.Title From Province p  where p.Id=APid),'') as AProvince,
		--ISNULL(ACid,0) AS ACid,
		--ISNULL((Select c.Title From city c  where c.Id=ACid),'') as ACity,
		ISNULL(Execdate,'1900-01-01') AS Execdate,
		ISNULL((select E.XZJB FROM employee E where E.id=Employee_CompanyMove.EId),'') AS XZJB
		,'单位异动' as memo

	from Employee_CompanyMove
	where eid=@eid and  ACompanyId is NOT NULL AND Audit = 1
	) as a
	order by ApplionTime
END
